<?php
if (!defined("BASEPATH")) exit("No direct script access allowed");

class Article_model extends MY_Model {

	function __construct() {
		parent::__construct();
		$this->tableName = "article";
		$this->prefix = "art";
	}

	function getArticle($where = NULL, $start = NULL, $limit = NULL, $fields = NULL, $query = NULL, $order = "artCreatetime", $dir = "DESC") {

		$fieldsQuery = Common::fieldsquery($fields, $query);

		$qs =	"
					select
						art_id, art_name, art_name_temp, art_url_name, art_url_name_temp, art_lead, art_lead_temp, art_text, art_text_temp, art_position, art_position_temp, art_temp_flag, szo_lname, art_highlight, art_highlight_temp, art_createtime, art_order, group_concat(distinct(ifnull(e2a_cou_id, '')) separator ',') as couList, group_concat(distinct(ifnull(e2a_city_id, '')) separator ',') as cityList, group_concat(distinct(ifnull(e2a_ins_id, '')) separator ',') as insList, group_concat(distinct(ifnull(e2a_maj_id, '')) separator ',') as majList, group_concat(distinct(ifnull(e2a_sub_id, '')) separator ',') as subList,
						
						group_concat(distinct(coalesce(cou_name, city_name, ins_name, maj_name)) order by coalesce(cou_name, city_name, ins_name, maj_name) separator ', ') as entList, 

						group_concat(distinct(coalesce(if(e2a_cou_id is not null, concat(con_url_name, '/', cou_url_name), NULL), if(e2a_city_id is not null, concat(city.cityConUrlName, '/', city.cityCouUrlName, '/', city_url_name), NULL), if(e2a_ins_id is not null, concat('institutes/', ins_url_name), NULL), if(e2a_maj_id is not null, concat('institutes/', maj.majInsUrlName, '/', maj_url_name), NULL) )) order by coalesce(cou_name, city_name, ins_name, maj_name) separator ', ') as entUrlList, 

						usr_email
					from
						".$this->db->dbprefix.$this->tableName."
						join ".$this->db->dbprefix."szotar on szo_sname = ".((in_array($this->uri->segment(1), array("admin", "admin_lte")) or $this->uri->segment(1) == "preview")?("coalesce(art_position_temp, art_position)"):("art_position"))." and szo_type = 'ARTPOS' and szo_del = 0
						
						".((in_array($this->uri->segment(1), array("admin", "admin_lte")) and Common::isAdmin())?
						(" join ".$this->db->dbprefix."ent2art on e2a_del = 0 and e2a_art_id = art_id and (
								".((isset($this->getUsr2ent["COU"]))?(" e2a_cou_id in (".implode(",", $this->getUsr2ent["COU"]["children"]).") or e2a_city_id in (select city_id from ".$this->db->dbprefix."city where city_del = 0 and city_cou_id in (".implode(",", $this->getUsr2ent["COU"]["children"]).") ) "):(" 1 = 2 "))." or
								".((isset($this->getUsr2ent["CITY"]))?(" e2a_city_id in (".implode(",", $this->getUsr2ent["CITY"]["children"]).") "):(" 1 = 2 "))." or
								".((isset($this->getUsr2ent["INS"]))?(" e2a_ins_id in (".implode(",", $this->getUsr2ent["INS"]["children"]).") "):(" 1 = 2 "))." or
								".((isset($this->getUsr2ent["INS"]))?(" e2a_maj_id in (select maj_id from ".$this->db->dbprefix."major where maj_del = 0 and maj_ins_id in (".implode(",", $this->getUsr2ent["INS"]["children"]).") ) "):(" 1 = 2 "))." or
								".((isset($this->getUsr2ent["INS"]))?(" e2a_sub_id in (select maj_sub_id from ".$this->db->dbprefix."major where maj_del = 0 and maj_ins_id in (".implode(",", $this->getUsr2ent["INS"]["children"]).") ) "):(" 1 = 2 "))."
							)
						"):( "left join ".$this->db->dbprefix."ent2art on art_id = e2a_art_id and e2a_del = 0 and e2a_temp_flag = ".((in_array($this->uri->segment(1), array("admin", "admin_lte")) or $this->uri->segment(1) == "preview")?("if(art_temp_flag = 'I', 'I', 'N')"):("'N'")) ))."
						
						left join ".$this->db->dbprefix."country on cou_id = e2a_cou_id and cou_del = 0 ".((in_array($this->uri->segment(1), array("admin", "admin_lte")))?(""):("and cou_presenter = 'I'"))."
						left join ".$this->db->dbprefix."city on city_id = e2a_city_id and city_del = 0 ".((in_array($this->uri->segment(1), array("admin", "admin_lte")))?(""):("and city_presenter = 'I'"))."

						left join ".$this->db->dbprefix."user on usr_id = art_usr_id and usr_del = 0
						left join ".$this->db->dbprefix."continent on (con_id = cou_con_id) and con_del = 0

						left join (
							select
								cou_id, cou_url_name as cityCouUrlName, con_url_name as cityConUrlName
							from
								".$this->db->dbprefix."country
								join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0
							where
								cou_del = 0
						) as city on city_cou_id = city.cou_id

						left join ".$this->db->dbprefix."institute on ins_id = e2a_ins_id and ins_del = 0 ".((in_array($this->uri->segment(1), array("admin", "admin_lte")))?(""):("and ins_presenter = 'I'"))."

						left join ".$this->db->dbprefix."major on maj_id = e2a_maj_id and maj_del = 0 ".((in_array($this->uri->segment(1), array("admin", "admin_lte")))?(""):("and maj_presenter = 'I'"))."

						left join (
							select
								ins_id, ins_url_name as majInsUrlName, city_url_name as majCityUrlName, cou_url_name as majCouUrlName, con_url_name as majConUrlName
							from
								".$this->db->dbprefix."institute
								join ".$this->db->dbprefix."city on city_id = ins_city_id and city_del = 0
								join ".$this->db->dbprefix."country on cou_id = city_cou_id and cou_del = 0
								join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0
							where
								ins_del = 0
						) as maj on maj_ins_id = maj.ins_id

					where
						art_del = 0 ".((!is_null($where))?(" and ".$where):(""))." ".(($fieldsQuery)?(" and (".$fieldsQuery.") "):(""))."
						".((in_array($this->uri->segment(1), array("admin", "admin_lte")) and Common::isAdmin())?(" and art_usr_id = '".$this->session->userdata("usrId")."' "):(""))."
					group by
						art_id
					order by
						".(($order == "rand()")?($order):(Common::underscore($order)." ".$dir))."
					".((is_numeric($start))?(" limit ".$start.",".$limit):(""))."
					";
					
					/* - 130923
						left join (
							select
								city_id, city_url_name as insCityUrlName, cou_url_name as insCouUrlName, con_url_name as insConUrlName
							from
								".$this->db->dbprefix."city
								join ".$this->db->dbprefix."country on cou_id = city_cou_id and cou_del = 0
								join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0
							where
								city_del = 0
						) as ins on ins_city_id = ins.city_id
					*/
					
		$result = $this->db->query($qs);
		//Common::pr($this->db->last_query());

		$getArticle = array();
		if($result->num_rows()) {
			foreach ($result->result() as $row) {

				$getArticle[] = array(
					"artId" => $row->art_id,
					"artName" => (((in_array($this->uri->segment(1), array("admin", "admin_lte")) or $this->uri->segment(1) == "preview") and $row->art_temp_flag == "I")?($row->art_name_temp):($row->art_name)),
					"artUrlName" => $row->art_url_name,
					"artLead" => (((in_array($this->uri->segment(1), array("admin", "admin_lte")) or $this->uri->segment(1) == "preview") and $row->art_temp_flag == "I")?($row->art_lead_temp):($row->art_lead)),
					"artText" => (((in_array($this->uri->segment(1), array("admin", "admin_lte")) or $this->uri->segment(1) == "preview") and $row->art_temp_flag == "I")?($row->art_text_temp):($row->art_text)),
					"artHighlight" => (((in_array($this->uri->segment(1), array("admin", "admin_lte")) or $this->uri->segment(1) == "preview") and $row->art_temp_flag == "I")?($row->art_highlight_temp):($row->art_highlight)),
					"artCreatetime" => $row->art_createtime,
					"couList"	=> $row->couList,
					"cityList"	=> $row->cityList,
					"insList"	=> $row->insList,
					"majList"	=> $row->majList,
					"subList"	=> $row->subList,
					"entList"	=> $row->entList,
					"entUrlList"	=> $row->entUrlList,
					"usrEmail" => $row->usr_email,
					"artPosition" => (((in_array($this->uri->segment(1), array("admin", "admin_lte")) or $this->uri->segment(1) == "preview") and $row->art_temp_flag == "I")?($row->art_position_temp):($row->art_position)),
					"szoLname" => $row->szo_lname,
					"artTempFlag" => $row->art_temp_flag,
					"artOrder" => $row->art_order,
				);

			}
		}

		return $getArticle;
	}
	
	function getArticleById($id) {
		$this->db->select("*");
		$this->db->from($this->tableName);
		$this->db->where("art_id", intval($id));
		$result = $this->db->get();
		return $result->row();
	}

	function getArticleCount($where = NULL, $fields, $query) {

		$fieldsQuery = Common::fieldsquery($fields, $query);

		$qs =	"
					select
						count(distinct art_id) as cnt
					from
						".$this->db->dbprefix.$this->tableName."
						join ".$this->db->dbprefix."szotar on szo_sname = ".((in_array($this->uri->segment(1), array("admin", "admin_lte")) or $this->uri->segment(1) == "preview")?("coalesce(art_position_temp, art_position)"):("art_position"))." and szo_type = 'ARTPOS' and szo_del = 0
						".((in_array($this->uri->segment(1), array("admin", "admin_lte")) and Common::isAdmin())?
						(" join ".$this->db->dbprefix."ent2art on e2a_del = 0 and e2a_art_id = art_id and (
								".((isset($this->getUsr2ent["COU"]))?(" e2a_cou_id in (".implode(",", $this->getUsr2ent["COU"]["children"]).") or e2a_city_id in (select city_id from ".$this->db->dbprefix."city where city_del = 0 and city_cou_id in (".implode(",", $this->getUsr2ent["COU"]["children"]).") ) "):(" 1 = 2 "))." or
								".((isset($this->getUsr2ent["CITY"]))?(" e2a_city_id in (".implode(",", $this->getUsr2ent["CITY"]["children"]).") "):(" 1 = 2 "))." or
								".((isset($this->getUsr2ent["INS"]))?(" e2a_ins_id in (".implode(",", $this->getUsr2ent["INS"]["children"]).") "):(" 1 = 2 "))." or
								".((isset($this->getUsr2ent["INS"]))?(" e2a_maj_id in (select maj_id from ".$this->db->dbprefix."major where maj_del = 0 and maj_ins_id in (".implode(",", $this->getUsr2ent["INS"]["children"]).") ) "):(" 1 = 2 "))." or
								".((isset($this->getUsr2ent["INS"]))?(" e2a_sub_id in (select maj_sub_id from ".$this->db->dbprefix."major where maj_del = 0 and maj_ins_id in (".implode(",", $this->getUsr2ent["INS"]["children"]).") ) "):(" 1 = 2 "))."
							)
						"):( "left join ".$this->db->dbprefix."ent2art on art_id = e2a_art_id and e2a_del = 0 and e2a_temp_flag = ".((in_array($this->uri->segment(1), array("admin", "admin_lte")) or $this->uri->segment(1) == "preview")?("if(art_temp_flag = 'I', 'I', 'N')"):("'N'")) ))."
					where
						art_del = 0 ".((!is_null($where))?(" and ".$where):(""))." ".(($fieldsQuery)?(" and (".$fieldsQuery.") "):(""))."
						".((in_array($this->uri->segment(1), array("admin", "admin_lte")) and Common::isAdmin())?(" and art_usr_id = '".$this->session->userdata("usrId")."' "):(""))."
					";
		//Common::pr($qs);
		$result = $this->db->query($qs);

		if($result->num_rows()) {
			foreach ($result->result() as $row) {
				return $row->cnt;
			}
		}
	}

	function getTags($where = NULL) {
		/*
		$qs =	"
					select
						e2a_cou_id, e2a_city_id, e2a_ins_id, e2a_maj_id,
						con_name, cou_name, city_name, ins_name, maj_name,
						coalesce(con_name, cou_name, city_name, ins_name, maj_name) as name,
						con_url_name, cou_url_name, city_url_name, ins_url_name, maj_url_name, 
						city.cityCouUrlName as city_cou_url_name, city.cityConUrlName as city_con_url_name,
						ins.insCityUrlName as ins_city_url_name, ins.insCouUrlName as ins_cou_url_name, ins.insConUrlName as ins_con_url_name,
						maj.majInsUrlName as maj_ins_url_name, maj.majCityUrlName as maj_city_url_name, maj.majCouUrlName as maj_cou_url_name, maj.majConUrlName as maj_con_url_name,
						count(*) as cnt
					from
						".$this->db->dbprefix.$this->tableName."
						left join ".$this->db->dbprefix."ent2art on art_id = e2a_art_id and e2a_del = 0
						left join ".$this->db->dbprefix."country on (cou_id = e2a_cou_id) and cou_del = 0
						left join ".$this->db->dbprefix."city on (city_id = e2a_city_id or cou_id = city_cou_id) and city_del = 0

						left join (
							select
								cou_id, cou_url_name as cityCouUrlName, con_url_name as cityConUrlName
							from
								".$this->db->dbprefix."country
								join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0
							where
								cou_del = 0
						) as city on city_cou_id = city.cou_id

						left join ".$this->db->dbprefix."institute on (ins_id = e2a_ins_id or city_id = ins_city_id) and ins_del = 0

						left join (
							select
								city_id, city_url_name as insCityUrlName, cou_url_name as insCouUrlName, con_url_name as insConUrlName
							from
								".$this->db->dbprefix."city
								join ".$this->db->dbprefix."country on cou_id = city_cou_id and cou_del = 0
								join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0
							where
								city_del = 0
						) as ins on ins_city_id = ins.city_id

						left join ".$this->db->dbprefix."major on (maj_id = e2a_maj_id or ins_id = maj_ins_id) and maj_del = 0

						left join (
							select
								ins_id, ins_url_name as majInsUrlName, city_url_name as majCityUrlName, cou_url_name as majCouUrlName, con_url_name as majConUrlName
							from
								".$this->db->dbprefix."institute
								join ".$this->db->dbprefix."city on city_id = ins_city_id and city_del = 0
								join ".$this->db->dbprefix."country on cou_id = city_cou_id and cou_del = 0
								join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0
							where
								ins_del = 0
						) as maj on maj_ins_id = maj.ins_id

						left join ".$this->db->dbprefix."continent on (con_id = cou_con_id) and con_del = 0
					where
						art_del = 0 and coalesce(cou_name, city_name, ins_name, maj_name) is not null ".((!is_null($where))?(" and ".$where):(""))."
					group by
						e2a_cou_id, e2a_city_id, e2a_ins_id, e2a_maj_id
					order by
						name
					";

		//Common::pr($qs); exit;
		$result = $this->db->query($qs);

		$getTags = array();
		if($result->num_rows()) {
			foreach ($result->result() as $row) {
				$getTags["tags"][] = array(
					"url" => (($row->e2a_cou_id)?($row->con_url_name."/".$row->cou_url_name):(($row->e2a_city_id)?($row->city_con_url_name."/".$row->city_cou_url_name."/".$row->city_url_name):(($row->e2a_ins_id)?("institutes/".$row->ins_url_name):(($row->e2a_maj_id)?("institutes/".$row->maj_ins_url_name."/".$row->maj_url_name):(""))))),
					"name" => (($row->e2a_cou_id)?($row->cou_name):(($row->e2a_city_id)?($row->city_name):(($row->e2a_ins_id)?($row->ins_name):(($row->e2a_maj_id)?($row->maj_name):(""))))),
					"cnt" => $row->cnt,
				);
				$getTags["minCnt"] = ((isset($getTags["minCnt"]) and $getTags["minCnt"] > $row->cnt)?($row->cnt):((!isset($getTags["minCnt"]))?($row->cnt):($getTags["minCnt"])));
				$getTags["maxCnt"] = ((isset($getTags["maxCnt"]) and $getTags["maxCnt"] < $row->cnt)?($row->cnt):((!isset($getTags["maxCnt"]))?($row->cnt):($getTags["maxCnt"])));
			}
		}
		*/
		/*
		$qs =	"
					select
						e2a_cou_id, e2a_sub_id,
						con_name, cou_name, sub_name,
						coalesce(cou_name, sub_name) as name,
						con_url_name, cou_url_name, sub_url_name, 
						count(distinct maj_id) as cnt
					from
						".$this->db->dbprefix.$this->tableName."
						left join ".$this->db->dbprefix."ent2art on art_id = e2a_art_id and e2a_del = 0
						left join ".$this->db->dbprefix."country on (cou_id = e2a_cou_id) and cou_del = 0 and cou_presenter = 'I'
						left join ".$this->db->dbprefix."city on (cou_id = city_cou_id) and city_del = 0 and city_presenter = 'I'
						left join ".$this->db->dbprefix."institute on (ins_city_id = city_id) and ins_del = 0 and ins_presenter = 'I'
						
						left join ".$this->db->dbprefix."subject on (sub_id = e2a_sub_id) and sub_del = 0

						left join ".$this->db->dbprefix."major on ((e2a_sub_id is not null and maj_sub_id = sub_id) or (e2a_cou_id is not null and maj_ins_id = ins_id)) and maj_del = 0 and maj_presenter = 'I'
						
						left join ".$this->db->dbprefix."continent on (con_id = cou_con_id) and con_del = 0
					where
						art_del = 0 and coalesce(cou_name, sub_name) is not null ".((!is_null($where))?(" and ".$where):(""))."
					group by
						e2a_cou_id, e2a_sub_id
					order by
						name
					";
		*/
		$qs =	"
					select
						concat('cou_', cou_id) as id,
						cou_name as name,
						concat(con_url_name, '/', cou_url_name) as url,
						count(distinct maj_id) as cnt
					from
						".$this->db->dbprefix."country
						join ".$this->db->dbprefix."city on cou_id = city_cou_id and city_del = 0 and city_presenter = 'I'
						join ".$this->db->dbprefix."institute on ins_city_id = city_id and ins_del = 0 and ins_presenter = 'I'
						join ".$this->db->dbprefix."major on maj_ins_id = ins_id and maj_del = 0 and maj_presenter = 'I'
						join ".$this->db->dbprefix."subject on sub_id = maj_sub_id and sub_del = 0
						join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0
					where
						cou_presenter = 'I' and cou_del = 0 ".((!is_null($where))?(" and ".$where):(""))."
					group by
						cou_id
						
					union all
					
					select
						concat('sub_', sub_id) as id,
						sub_name as name,
						concat('majors/', sub_url_name) as url, 
						count(distinct maj_id) as cnt
					from
						".$this->db->dbprefix."country
						join ".$this->db->dbprefix."city on cou_id = city_cou_id and city_del = 0 and city_presenter = 'I'
						join ".$this->db->dbprefix."institute on ins_city_id = city_id and ins_del = 0 and ins_presenter = 'I'
						join ".$this->db->dbprefix."major on maj_ins_id = ins_id and maj_del = 0 and maj_presenter = 'I'
						join ".$this->db->dbprefix."subject on sub_id = maj_sub_id and sub_del = 0
						join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0
					where
						cou_presenter = 'I' and cou_del = 0 ".((!is_null($where))?(" and ".$where):(""))."
					group by
						sub_id
						
					order by
						name
					";
		//Common::pr($qs); exit;
		$result = $this->db->query($qs);

		$getTags = array();
		if($result->num_rows()) {
			foreach ($result->result() as $row) {
				$getTags["tags"][] = array(
					"url" => $row->url,
					"name" => $row->name. "&nbsp;(".$row->cnt.")",
					"cnt" => $row->cnt,
				);
				$getTags["minCnt"] = ((isset($getTags["minCnt"]) and $getTags["minCnt"] > $row->cnt)?($row->cnt):((!isset($getTags["minCnt"]))?($row->cnt):($getTags["minCnt"])));
				$getTags["maxCnt"] = ((isset($getTags["maxCnt"]) and $getTags["maxCnt"] < $row->cnt)?($row->cnt):((!isset($getTags["maxCnt"]))?($row->cnt):($getTags["maxCnt"])));
			}
		}		
		
		//Common::pr($getTags); exit;
		return $getTags;
	}

	function getArticleByEntId($where) {
		$return = array();
		$this->db->select("art_url_name, art_name");
		$this->db->from($this->tableName);
		$this->db->join("ent2art", "ent2art.e2a_art_id = article.art_id and e2a_del = 0");
		$this->db->where($where);
		$this->db->where($this->prefix."_name is not null", NULL);
		$this->db->group_by("art_id");
		$this->db->order_by("art_createtime", "DESC");
		$result = $this->db->get();
		if($result->num_rows()) {
			foreach ($result->result() as $row) {
				$return[] = $row;
			}
		}
		return $return;
	}
	
	function fixOrder() {
		$getArticle = $this->getArticle($where = "(art_highlight = 'I' or art_position in ('H', 'F', 'R'))", $start = NULL, $limit = NULL, $fields = NULL, $query = NULL, $order = "artOrder", $dir = "ASC");
		$arrType = array();
		foreach($getArticle as $art) {
			if($art["artHighlight"] == "I") {
				$art["artPosition"] == "Highlight";
			}
			$arrType[$art["artPosition"]] = ((isset($arrType[$art["artPosition"]]))?($arrType[$art["artPosition"]]+1):(1));
			$this->update($art["artId"], array("art_order" => $arrType[$art["artPosition"]]));
			//echo $art["artId"] ." = ". $arrType[$art["artPosition"]]."<br/>";
		}
	}
	
}